--------------------------------------------------------------------------------
-- @name: bitmap index
-- @author: dion cho
-- @note: test case for the bitmap index and not null
--------------------------------------------------------------------------------

set pages 10000
set lines 120
set timing off
set trimspool on

col item format a30
col deltas format a50

@capture_on

-- bitmap index and not null
drop table t1 purge;

create table t1 
as
select
	to_number(decode(mod(level, 11), 0, NULL, mod(level, 10))) as c1,
	mod(level, 10000) as c2,
	rpad('x', 10, 'x') as dummy
from dual
connect by level <= 100000;

create bitmap index t1_b1 on t1(c1);
create bitmap index t1_b2 on t1(c2);
	

exec dbms_stats.gather_table_stats(user, 't1');


-- for low cardinality 
explain plan for 
select /*+ full(t1) */ *
from t1
where c1 is null;	

select * from table(dbms_xplan.display);


explain plan for 
select /*+ index(t1 t1(c1)) */ *
from t1
where c1 is null;	

select * from table(dbms_xplan.display);


explain plan for 
select /*+ index(t1 t1(c1)) */ *
from t1
where c1 is not null;	

select * from table(dbms_xplan.display);


explain plan for 
select /*+ index(t1 t1(c2)) */ *
from t1
where c2 is null;	

select * from table(dbms_xplan.display);


explain plan for 
select /*+ index(t1 t1(c2)) */ *
from t1
where c2 is not null;	

select * from table(dbms_xplan.display);


-- when going to the table
-- c1
select /*+ gather_plan_statistics full(t1) */ 
	count(dummy)
from t1
where c1 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c1)) */
	count(dummy)
from t1
where c1 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c1)) */
	count(dummy)
from t1
where c1 is not null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- c2
select /*+ gather_plan_statistics index(t1 t1(c2)) */
	count(dummy)
from t1
where c2 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c2)) */
	count(dummy)
from t1
where c2 is not null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



-- when inex only
-- c1
select /*+ gather_plan_statistics full(t1) */ 
	count(*)
from t1
where c1 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c1)) */
	count(*)
from t1
where c1 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c1)) */
	count(*)
from t1
where c1 is not null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- c2
select /*+ gather_plan_statistics index(t1 t1(c2)) */
	count(*)
from t1
where c2 is null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1(c2)) */
	count(*)
from t1
where c2 is not null;	

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- bitmap operation
drop table t1 purge;

create table t1 
as
select
	mod(level, 100) as c1,
	100 - mod(level, 100) as c2,
	rpad('x', 10, 'x') as dummy
from dual
connect by level <= 100000;

create bitmap index t1_b1 on t1(c1);
create bitmap index t1_b2 on t1(c2);
	

exec dbms_stats.gather_table_stats(user, 't1');


select /*+ gather_plan_statistics full(t1) */ 
	count(dummy)
from t1
where c1 = 1 and c2 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t1 t1_b1) no_index(t1 t1_b2) */ 
	count(dummy)
from t1
where c1 = 1 and c2 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));




select /*+ gather_plan_statistics index_combine(t1 t1_b1 t1_b2) */ 
	count(dummy)
from t1
where c1 = 1 and c2 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



@capture_off
